package com.example.demo.controller;

import com.example.demo.annotation.AutoLog;
import com.example.demo.entity.BaseResponse;
import com.example.demo.entity.Database;
import com.example.demo.entity.SysUser;
import com.example.demo.entity.Teacher;
import com.example.demo.service.DatabaseService;
import com.example.demo.service.QuestionService;
import com.example.demo.service.TeacherService;
import com.example.demo.tool.*;
import com.example.demo.util.*;

import lombok.Data;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.web.bind.annotation.*;

import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Data
class InsertQuestion {
	String dbName;
	String teacherId;
    short questionClass;
    String title;
    String content;
    String targetName;
    String answer;
    String analysis;
    String initSQL;
    String difficulty;
    List<String> testCases;
}

@RequestMapping("/question")
@RestController
public class QuestionController {
    @Autowired
    private QuestionService questionService;
    @Autowired
    private DatabaseService databaseService;
    @Autowired
    private TeacherService teacherService;
    @Autowired
    private DBDatasource dbDatasource;

    /*新增试题*/
    @AutoLog("新增试题")
    @PostMapping(value = "/addNewQuestion")
    @Transactional
    public BaseResponse<Map<String,Object>> insertQuestion(@RequestBody InsertQuestion question
    		                /*@RequestParam(value = "dbName") String dbName,
    		                  @RequestParam(value = "teacherId") String teacherId,
                              @RequestParam(value = "questionClass") short questionClass,
                              @RequestParam(value = "title") String title,
                              @RequestParam(value = "content") String content,
                              @RequestParam(value = "targetName", required = false) String targetName,
                              @RequestParam(value = "answer") String answer,
                              @RequestParam(value = "analysis", required = false) String analysis,
                              @RequestParam(value = "initSQL", required = false) String initSQL,
                              @RequestParam(value = "testCases", required = false) List<String> testCases*/
                                )throws ParseException {
    	String dbName = question.getDbName();
    	String teacherId = question.getTeacherId();
        short questionClass = question.getQuestionClass();
        String title = question.getTitle();
        String content = question.getContent();
        String targetName = question.getTargetName();
        String answer = question.getAnswer();
        String analysis = question.getAnalysis();
        String initSQL = question.getInitSQL();
        String difficulty = question.getDifficulty();
        List<String> testCases =question.getTestCases();
    	
        Map<String,Object> result=questionService.insertQuestion(dbName, teacherId, questionClass, title, content, targetName, answer, analysis, initSQL,difficulty);
    	if(!"success".equals(result.get("state"))){
            return BaseResponse.fail(result);
        }
        
        /* 非评判函数类题目，测试用例为null或为空，新增题目结束 */
        if (questionClass != 12 || testCases == null || testCases.size() == 0) {
        	return  BaseResponse.success(result);
        }
        /* 获取题目Id */
        int questionId = (int)result.get("questionId");
        
        /* 配置题目所在数据库连接信息 */
        Database database = databaseService.getDatabaseByName(dbName);
        Teacher teacher = teacherService.getTeacher(database.getUserId());
        DBConnection dbConnection = new DBConnection(dbDatasource);
        SetDBConnection.setDBConnection(dbConnection, database, teacher);
        
        Connection connection;
        /* 打开数据库连接并设置为非自动提交 */
        try {
        	connection = dbConnection.getConnect();
        	connection.setAutoCommit(false);
        } catch (SQLException e) {
        	e.printStackTrace();
        	TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
        	Map<String, Object> modelMap = new HashMap<>();
        	modelMap.put("errmessage", "连接答题数据库失败");
			modelMap.put("detail", e.getMessage());
			return BaseResponse.fail(modelMap);
        }
        
        DBJudgeTool dbJudgeTool = new DBJudgeTool(connection);
        
        /* 执行insert语句 */
        result = dbJudgeTool.insertFuncTests(questionId, testCases);
        if (!"success".equals(result.get("state"))) {
        	TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
        	try {
        		connection.rollback();
        		dbConnection.close();
        	} catch (SQLException e) {
        		e.printStackTrace();
        	}
        	return BaseResponse.fail(result);
        }
        
        /* 提交事务，并关闭数据库连接 */
        try {
        	connection.commit();
        	dbConnection.close();
        } catch (SQLException e) {
        	e.printStackTrace();
        	TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
        	Map<String, Object> modelMap = new HashMap<>();
        	modelMap.put("errmessage", "连接答题数据库失败");
			modelMap.put("detail", e.getMessage());
			return BaseResponse.fail(modelMap);
        }
        
        return  BaseResponse.success(result);
    }
    
    /*逻辑删除试题*/
    @AutoLog("逻辑删除试题")
    @PostMapping(value = "/delQuestion")
    public BaseResponse<Map<String,Object>> delQuestion(@RequestParam(value = "id") int id)throws ParseException{
    	if(questionService != null) 
    	    questionService.getAllQuestion();
    	Map<String,Object> result=questionService.delQuestion(id);
        if(result == null || result.get("state")!="success"){
            return BaseResponse.fail();
        }
        return  BaseResponse.success(result);

    }
    
    /*修改试题*/
    @AutoLog("修改试题")
    @PostMapping(value = "/modifyQuestion")
    public BaseResponse<Map<String,Object>> modifyQuestion(@RequestParam(value = "id") String id,
                                              @RequestParam(value = "dbName") String dbName,
                                              @RequestParam(value = "questionClass") short questionClass,
                                              @RequestParam(value = "title") String title,
                                              @RequestParam(value = "content") String content,
                                              @RequestParam(value = "targetName", required = false) String targetName,
                                              @RequestParam(value = "answer") String answer,
                                              @RequestParam(value = "analysis", required = false) String analysis,
                                              @RequestParam(value = "initSQL", required = false) String initSQL,
                                              @RequestParam(value = "difficulty", required = false) String difficulty
    		                                  )throws ParseException{

        Map<String,Object> result=questionService.modifyQuestion(id, dbName, questionClass, title, content, targetName, answer, analysis, initSQL,difficulty);

        if(result.get("state")!="success"){
            return BaseResponse.fail(result);
        }
        return  BaseResponse.success(result);

    }
    
    /*查询指定试题内容（教师）*/
    @AutoLog("查询指定试题内容（教师）")
    @GetMapping(value = "/getQuestionTeacher")
    public BaseResponse<Map<String,Object>> getQuestionTeacher(@RequestParam(value = "id") int id)throws ParseException{

        return BaseResponse.success(questionService.getQuestionTeacher(id));

    }
    
    /*查询指定试题内容（学生）*/
    @AutoLog("查询指定试题内容（学生）")
    @GetMapping(value = "/getQuestionStudent")
    public BaseResponse<Map<String,Object>> getQuestionStudent(@RequestParam(value = "id") int id)throws ParseException{

       return BaseResponse.success(questionService.getQuestionStudent(id));
    }
    
    /*查询指定试题解析（学生）*/
    @AutoLog("查询指定试题解析（学生）")
    @GetMapping(value = "/getQuestionAnalyse")
    public BaseResponse<Map<String,Object>> getQuestionAnalyse(@RequestParam(value = "id") int id)throws ParseException{
        return BaseResponse.success(questionService.getQuestionAnalyse(id));
    }
    
    /*查询所有试题*/
    @AutoLog("查询所有试题")
    @GetMapping(value = "/getAllQuestion")
    public BaseResponse<List<Map<String,Object>>>getAllQuestion(){
        List<Map<String,Object>> results=questionService.getAllQuestion();
//        if(results.size()==0){
//            return BaseResponse.fail();
//        }
        return BaseResponse.success(results);

    }

    /* 物理删除指定试题 */
    @AutoLog("物理删除指定试题")
    @PostMapping(value = "/deleteQuestionPhysically")
    @Transactional
    public BaseResponse<Map<String, Object>> deleteQuestionPhysically(@RequestParam(value = "id") int id) {
    	/* 删除试题表中的记录 */
    	Map<String, Object> result = questionService.deleteQuestionPhysically(id);
    	if(!"success".equals(result.get("state"))){
            return BaseResponse.fail(result);
        }
    	
    	/* 判断题目类型，如果是函数/存储过程题目则执行下一步 */
    	short questionClass = (short)result.get("questionClass");
    	if (questionClass != 12) {
    		return BaseResponse.success(null);
    	}
    	String dbName = (String)result.get("dbName");
    	
    	Database database = databaseService.getDatabaseByName(dbName);
    	Teacher teacher = teacherService.getTeacher(database.getUserId());
    	DBConnection dbConnection = new DBConnection(dbDatasource);
    	SetDBConnection.setDBConnection(dbConnection, database, teacher);
    	
    	/* 连接数据库 */
    	Connection connection;
    	try {
    		connection = dbConnection.getConnect();
    	} catch (SQLException e) {
    		e.printStackTrace();
    		TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
    		Map<String, Object> modelMap = new HashMap<>();
        	modelMap.put("errmessage", "连接答题数据库失败");
			modelMap.put("detail", e.getMessage());
			return BaseResponse.fail(modelMap);
    	}
    	
    	/* 删除记录 */
    	DBJudgeTool dbJudgeTool = new DBJudgeTool(connection);
    	
    	result = dbJudgeTool.deleteAllFuncTest(id);
    	if("error".equals(result.get("state"))){
    		TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
    		try {
        		dbConnection.close();
        	} catch (SQLException e) {
        		e.printStackTrace();
        	}
        	return BaseResponse.fail(result);
        }
    	
    	/* 关闭数据库连接 */
    	try {
    		dbConnection.close();
    	} catch (SQLException e) {
			e.printStackTrace();
		}
    	
    	return BaseResponse.success(result);
    }
    
    /* 查询所有试题（包括已设为删除状态的题目） */
    @AutoLog("查询所有试题（包括已设为删除状态的题目）")
    @GetMapping(value = "/getQuestions")
    public BaseResponse<List<Map<String, Object>>> getQuestion() {
    	
    	return BaseResponse.success(questionService.getQuestions());
    }


    /*生成测试样例*/
    @AutoLog("生成测试样例")
    @GetMapping(value = "/sendQuestionNumber")
    public BaseResponse<Map<String, Object>> sendQuestionNumber(@RequestParam(value = "questionId") int questionId ,
                                                                @RequestParam(value = "dbName") String dbName,
                                                                @RequestParam(value = "zjId",required = false,defaultValue = "#") String zjId,
                                                                @RequestParam(value = "teacherId") String teacherId) throws IOException {
        Integer isSuccess = 0;

        Map<String,Object> questionDetail = questionService.getQuestionTeacher(questionId);

        String describe = (String) questionService.getQuestionTeacher(questionId).get("describe");

        String content = (String) questionService.getQuestionTeacher(questionId).get("content");

        String userId;

        String code;

        Map<String, Object> modelMap = new HashMap<>();

        //String sql = generateSql(describe , content); //创建测试样例的SQL

        GenerateTestCase generateTestCase = new GenerateTestCase();

        try{
            code = generateTestCase.generateSql(describe , content).getResult();
            System.out.println("**********code***********");
            System.out.println(code);
        } catch(IOException e) {
            modelMap.put("errmessage", "http请求访问chatgpt失败");
            return BaseResponse.fail(modelMap);
        }

        //String code = "select * from a";

        System.out.println(code);

        if (zjId.equals("#")){
            userId = teacherId;
        }
        else{
            userId = zjId;
        }

        DBConnection dbConnection = (DBConnection) LocalCacheUtil.get(userId);

        System.out.println("LocalCacheUtil.get(userId)888");
        System.out.println(userId);
        System.out.println(LocalCacheUtil.get(userId));


        if (dbConnection == null) {
//			modelMap.put("errmessage", "connection timeout, please reconnect database");
            modelMap.put("errmessage", "连接超时, 请重连数据库");
            return BaseResponse.fail(modelMap);
        }

        try {
            Connection connection = dbConnection.getConnect();
            DBExecTool dbExecTool = new DBExecTool(connection);
            ArrayList<String> queryList = new ArrayList<>();
            List<Map<String, Object>> resultList = new ArrayList<>();
            SQLTerminalQuerySplit sqlTerminalQuerySplit = new SQLTerminalQuerySplit();
            RemoveComment removeComment = new RemoveComment();


            sqlTerminalQuerySplit.splitQuerries(queryList, code, false);
            System.out.println("----------------------------------------");
            System.out.println(queryList.size());
            int n = queryList.size();
            if(n>1024) {
                modelMap.put("errmessage", "执行语句大于1024条，请检查后重新输入");
                return BaseResponse.fail(modelMap);
            }
            for(String singleSQL : queryList) {
                singleSQL = removeComment.removeComment(singleSQL).trim();

                if (singleSQL.isEmpty()) continue;
                System.out.println(singleSQL);
                System.out.println(singleSQL);
                Map<String, Object> sqlResult = dbExecTool.execute(singleSQL);
                if (sqlResult.get("ResultType").equals("ERROR")){
                    modelMap.put("errmessage", "chatgpt生成的SQL语句出现错误，无法成功执行，请重新创建答题数据库并重试");
                    return BaseResponse.fail(modelMap);
                }
                sqlResult.put("supportUpdate", JSQLParserUtils.isQueryResultEditSupported(singleSQL));
                resultList.add(sqlResult);
//				if (sqlResult.get("ResultType").equals("ERROR"))
//					break;
            }
            modelMap.put("resultList", resultList);
            System.out.println(resultList);
        } catch(SQLException e) {
            e.printStackTrace();
//			modelMap.put("errmessage", "database connect error");
            modelMap.put("errmessage", "数据库连接错误");
            return BaseResponse.fail(modelMap);
        }

        System.out.println("modelMap");
        System.out.println(modelMap);

        return BaseResponse.success(modelMap);

        //Boolean result = excuteSql();

        //return BaseResponse.success(result);
    }
}
